/* Begin - ReqID:SRS-SQL-SYSVIEW */
/* PROSRC LINES COUNT */
CREATE OR REPLACE FUNCTION SYS._ORA_SRC_COUNT(PROID OID)
RETURNS INTEGER AS $$
DECLARE
	RET INTEGER;
BEGIN
	SELECT
		COUNT(1)
	FROM
	(
		SELECT
			UNNEST(STRING_TO_ARRAY(PROSRC, CHR(10)))
		FROM
			PG_PROC
		WHERE
			OID = $1
	) AS SQ
	INTO RET;
	RETURN RET;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

/* DBA_SOURCE */
CREATE OR REPLACE VIEW SYS.DBA_SOURCE AS
SELECT
	SYS.ORA_CASE_TRANS(
		PG_GET_USERBYID(P.PROOWNER)::VARCHAR2
	)::VARCHAR2(128) AS OWNER,
	SYS.ORA_CASE_TRANS(P.PRONAME::VARCHAR2)::VARCHAR2(128) AS NAME,
	CASE
		WHEN P.PROKIND = 'f'
			THEN 'FUNCTION'
		ELSE 'PROCEDURE'
	END::VARCHAR2(12) AS TYPE,
	GENERATE_SERIES(1::INTEGER, SYS._ORA_SRC_COUNT(P.OID))::NUMBER AS LINE,
	UNNEST(STRING_TO_ARRAY(P.PROSRC, CHR(10)))::VARCHAR2(4000) AS TEXT,
	0::VARCHAR2(256) AS ORIGIN_CON_ID
FROM
	PG_PROC AS P
	LEFT JOIN PG_TRIGGER AS T
		ON P.OID = T.TGFOID
WHERE
	P.PROKIND = ANY(ARRAY['p', 'f'])
	AND T.OID IS NULL
	AND P.PRONAMESPACE <> 'PG_CATALOG'::REGNAMESPACE::OID
	AND P.PRONAMESPACE <> 'PG_TOAST'::REGNAMESPACE::OID
	AND P.PRONAMESPACE <> 'INFORMATION_SCHEMA'::REGNAMESPACE::OID
;

/* GRANT SELECT PRIVILEGE TO PUBLIC */
GRANT SELECT ON SYS.DBA_SOURCE TO PUBLIC;

/* ALL_SOURCE */
CREATE OR REPLACE VIEW SYS.ALL_SOURCE AS
SELECT
	SYS.ORA_CASE_TRANS(
		PG_GET_USERBYID(P.PROOWNER)::VARCHAR2
	)::VARCHAR2(128) AS OWNER,
	SYS.ORA_CASE_TRANS(P.PRONAME::VARCHAR2)::VARCHAR2(128) AS NAME,
	CASE
		WHEN P.PROKIND = 'f'
			THEN 'FUNCTION'
		ELSE 'PROCEDURE'
	END::VARCHAR2(12) AS TYPE,
	GENERATE_SERIES(1::INTEGER, SYS._ORA_SRC_COUNT(P.OID))::NUMBER AS LINE,
	UNNEST(STRING_TO_ARRAY(P.PROSRC, CHR(10)))::VARCHAR2(4000) AS TEXT,
	0::VARCHAR2(256) AS ORIGIN_CON_ID
FROM
	PG_PROC AS P
	LEFT JOIN PG_TRIGGER AS T
		ON P.OID = T.TGFOID
WHERE
	P.PROKIND = ANY(ARRAY['p', 'f'])
	AND T.OID IS NULL
	AND P.PRONAMESPACE <> 'PG_CATALOG'::REGNAMESPACE::OID
	AND P.PRONAMESPACE <> 'PG_TOAST'::REGNAMESPACE::OID
	AND P.PRONAMESPACE <> 'INFORMATION_SCHEMA'::REGNAMESPACE::OID
	AND HAS_FUNCTION_PRIVILEGE(P.OID, 'EXECUTE')
;

/* GRANT SELECT PRIVILEGE TO PUBLIC */
GRANT SELECT ON SYS.ALL_SOURCE TO PUBLIC;

/* USER_SOURCE */
CREATE OR REPLACE VIEW SYS.USER_SOURCE AS
SELECT
	SYS.ORA_CASE_TRANS(P.PRONAME::VARCHAR2)::VARCHAR2(128) AS NAME,
	CASE
		WHEN P.PROKIND = 'f'
			THEN 'FUNCTION'
		ELSE 'PROCEDURE'
	END::VARCHAR2(12) AS TYPE,
	GENERATE_SERIES(1::INTEGER, SYS._ORA_SRC_COUNT(P.OID))::NUMBER AS LINE,
	UNNEST(STRING_TO_ARRAY(P.PROSRC, CHR(10)))::VARCHAR2(4000) AS TEXT,
	0::VARCHAR2(256) AS ORIGIN_CON_ID
FROM
	PG_PROC AS P
	LEFT JOIN PG_TRIGGER AS T
		ON P.OID = T.TGFOID
WHERE
	P.PROKIND = ANY(ARRAY['p', 'f'])
	AND T.OID IS NULL
	AND P.PRONAMESPACE <> 'PG_CATALOG'::REGNAMESPACE::OID
	AND P.PRONAMESPACE <> 'PG_TOAST'::REGNAMESPACE::OID
	AND P.PRONAMESPACE <> 'INFORMATION_SCHEMA'::REGNAMESPACE::OID
	AND P.PROOWNER::REGROLE = CURRENT_USER::REGROLE
;

/* GRANT SELECT PRIVILEGE TO PUBLIC */
GRANT SELECT ON SYS.USER_SOURCE TO PUBLIC;
/* End - ReqID:SRS-SQL-SYSVIEW */
